from rest_framework.views import APIView
from rest_framework.response import Response
from rest_framework import status
from rest_framework.permissions import AllowAny
from django.db.models import Count, Avg, Sum, Q, F, Case, When, IntegerField, FloatField
from django.utils import timezone
from datetime import datetime, timedelta
from django.http import HttpResponse
from apps.users.models import User
from apps.courses.models import Course
from apps.enrollments.models import Enrollment
import logging
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill
from io import BytesIO

logger = logging.getLogger(__name__)


class StatisticsOverviewView(APIView):
    """统计概览视图 - 根据角色返回不同数据"""

    authentication_classes = []
    permission_classes = [AllowAny]

    def get(self, request):
        """获取统计概览数据"""
        try:
            user_id = request.META.get('HTTP_USER_ID')
            user_role = request.META.get('HTTP_USER_ROLE')

            if not user_id:
                return Response({
                    'code': 401,
                    'message': '未登录'
                }, status=status.HTTP_401_UNAUTHORIZED)

            # 获取查询参数
            semester = request.GET.get('semester', '')
            start_date = request.GET.get('start_date', '')
            end_date = request.GET.get('end_date', '')

            # 根据角色返回不同数据
            if user_role == 'admin':
                data = self._get_admin_overview(semester, start_date, end_date)
            elif user_role == 'teacher':
                data = self._get_teacher_overview(user_id, semester, start_date, end_date)
            elif user_role == 'student':
                data = self._get_student_overview(user_id, semester, start_date, end_date)
            else:
                return Response({
                    'code': 403,
                    'message': '无效的用户角色'
                }, status=status.HTTP_403_FORBIDDEN)

            return Response({
                'code': 200,
                'message': '获取统计概览成功',
                'data': data
            }, status=status.HTTP_200_OK)

        except Exception as e:
            logger.error(f"获取统计概览失败: {str(e)}")
            return Response({
                'code': 500,
                'message': '获取统计概览失败'
            }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

    def _get_admin_overview(self, semester, start_date, end_date):
        """管理员统计概览"""
        # 基础查询集
        course_queryset = Course.objects.all()
        enrollment_queryset = Enrollment.objects.all()
        user_queryset = User.objects.all()

        # 应用过滤条件
        if semester:
            course_queryset = course_queryset.filter(semester=semester)
            enrollment_queryset = enrollment_queryset.filter(course__semester=semester)

        if start_date and end_date:
            try:
                start_dt = datetime.strptime(start_date, '%Y-%m-%d')
                end_dt = datetime.strptime(end_date, '%Y-%m-%d')
                enrollment_queryset = enrollment_queryset.filter(
                    enrollment_date__range=[start_dt, end_dt]
                )
            except ValueError:
                pass

        # 基础统计
        total_users = user_queryset.count()
        total_students = user_queryset.filter(role='student').count()
        total_teachers = user_queryset.filter(role='teacher').count()
        total_courses = course_queryset.count()
        total_enrollments = enrollment_queryset.filter(status='enrolled').count()

        # 计算平均成绩
        avg_score = enrollment_queryset.filter(
            total_score__isnull=False
        ).aggregate(avg=Avg('total_score'))['avg'] or 0

        # 计算趋势数据（与上月对比）
        current_month = timezone.now().replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        last_month = (current_month - timedelta(days=1)).replace(day=1)

        current_month_enrollments = Enrollment.objects.filter(
            enrollment_date__gte=current_month,
            status='enrolled'
        ).count()

        last_month_enrollments = Enrollment.objects.filter(
            enrollment_date__gte=last_month,
            enrollment_date__lt=current_month,
            status='enrolled'
        ).count()

        enrollment_trend = 0
        if last_month_enrollments > 0:
            enrollment_trend = round(
                ((current_month_enrollments - last_month_enrollments) / last_month_enrollments) * 100, 1
            )

        # 计算其他趋势
        current_month_students = User.objects.filter(
            role='student',
            created_at__gte=current_month
        ).count()

        last_month_students = User.objects.filter(
            role='student',
            created_at__gte=last_month,
            created_at__lt=current_month
        ).count()

        student_trend = 0
        if last_month_students > 0:
            student_trend = round(
                ((current_month_students - last_month_students) / last_month_students) * 100, 1
            )

        return {
            'total_enrollments': total_enrollments,
            'enrollment_trend': enrollment_trend,
            'active_students': total_students,
            'student_trend': student_trend,
            'total_courses': total_courses,
            'course_trend': 0,  # 可以添加课程趋势计算
            'avg_score': round(avg_score, 2),
            'score_trend': 0,  # 可以添加成绩趋势计算
            'total_users': total_users,
            'total_teachers': total_teachers
        }

    def _get_teacher_overview(self, teacher_id, semester, start_date, end_date):
        """教师统计概览"""
        # 基础查询集
        course_queryset = Course.objects.filter(teacher_id=teacher_id)
        enrollment_queryset = Enrollment.objects.filter(course__teacher_id=teacher_id)

        # 应用过滤条件
        if semester:
            course_queryset = course_queryset.filter(semester=semester)
            enrollment_queryset = enrollment_queryset.filter(course__semester=semester)

        if start_date and end_date:
            try:
                start_dt = datetime.strptime(start_date, '%Y-%m-%d')
                end_dt = datetime.strptime(end_date, '%Y-%m-%d')
                enrollment_queryset = enrollment_queryset.filter(
                    enrollment_date__range=[start_dt, end_dt]
                )
            except ValueError:
                pass

        # 基础统计
        my_courses = course_queryset.count()
        my_students = enrollment_queryset.filter(status='enrolled').count()

        # 计算完成率
        total_enrolled = enrollment_queryset.filter(status__in=['enrolled', 'completed']).count()
        completed = enrollment_queryset.filter(status='completed').count()
        completion_rate = round((completed / total_enrolled * 100), 2) if total_enrolled > 0 else 0

        # 计算平均分
        avg_score = enrollment_queryset.filter(
            total_score__isnull=False
        ).aggregate(avg=Avg('total_score'))['avg'] or 0

        return {
            'my_courses': my_courses,
            'course_trend': 0,
            'my_students': my_students,
            'student_trend': 0,
            'completion_rate': completion_rate,
            'completion_trend': 0,
            'avg_score': round(avg_score, 2),
            'score_trend': 0
        }

    def _get_student_overview(self, student_id, semester, start_date, end_date):
        """学生统计概览"""
        # 基础查询集
        enrollment_queryset = Enrollment.objects.filter(student_id=student_id)

        # 应用过滤条件
        if semester:
            enrollment_queryset = enrollment_queryset.filter(course__semester=semester)

        if start_date and end_date:
            try:
                start_dt = datetime.strptime(start_date, '%Y-%m-%d')
                end_dt = datetime.strptime(end_date, '%Y-%m-%d')
                enrollment_queryset = enrollment_queryset.filter(
                    enrollment_date__range=[start_dt, end_dt]
                )
            except ValueError:
                pass

        # 基础统计
        enrolled_courses = enrollment_queryset.filter(status='enrolled').count()
        completed_courses = enrollment_queryset.filter(status='completed').count()

        # 计算已获学分
        earned_credits = Course.objects.filter(
            id__in=enrollment_queryset.filter(
                status__in=['completed'],
                total_score__gte=60
            ).values('course_id')
        ).aggregate(total=Sum('credit_hours'))['total'] or 0

        # 计算GPA
        graded_enrollments = enrollment_queryset.filter(total_score__isnull=False)
        total_credits = 0
        total_grade_points = 0

        for enrollment in graded_enrollments.select_related('course'):
            credit_hours = enrollment.course.credit_hours
            total_credits += credit_hours
            total_grade_points += enrollment.gpa_points * credit_hours

        gpa = round(total_grade_points / total_credits, 2) if total_credits > 0 else 0

        return {
            'enrolled_courses': enrolled_courses,
            'enrollment_trend': 0,
            'earned_credits': earned_credits,
            'credit_trend': 0,
            'gpa': gpa,
            'gpa_trend': 0,
            'completed_courses': completed_courses,
            'completion_trend': 0
        }


class StatisticsChartsView(APIView):
    """统计图表数据视图"""

    authentication_classes = []
    permission_classes = [AllowAny]

    def get(self, request):
        """获取图表数据"""
        try:
            user_id = request.META.get('HTTP_USER_ID')
            user_role = request.META.get('HTTP_USER_ROLE')

            if not user_id:
                return Response({
                    'code': 401,
                    'message': '未登录'
                }, status=status.HTTP_401_UNAUTHORIZED)

            # 获取查询参数
            semester = request.GET.get('semester', '')
            start_date = request.GET.get('start_date', '')
            end_date = request.GET.get('end_date', '')

            # 根据角色返回不同数据
            if user_role == 'admin':
                data = self._get_admin_charts(semester, start_date, end_date)
            elif user_role == 'teacher':
                data = self._get_teacher_charts(user_id, semester, start_date, end_date)
            elif user_role == 'student':
                data = self._get_student_charts(user_id, semester, start_date, end_date)
            else:
                return Response({
                    'code': 403,
                    'message': '无效的用户角色'
                }, status=status.HTTP_403_FORBIDDEN)

            return Response({
                'code': 200,
                'message': '获取图表数据成功',
                'data': data
            }, status=status.HTTP_200_OK)

        except Exception as e:
            logger.error(f"获取图表数据失败: {str(e)}")
            return Response({
                'code': 500,
                'message': '获取图表数据失败'
            }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

    def _get_admin_charts(self, semester, start_date, end_date):
        """管理员图表数据"""
        # 基础查询集
        course_queryset = Course.objects.all()
        enrollment_queryset = Enrollment.objects.all()

        # 应用过滤条件
        if semester:
            course_queryset = course_queryset.filter(semester=semester)
            enrollment_queryset = enrollment_queryset.filter(course__semester=semester)

        # 1. 选课人数统计
        enrollment_count = course_queryset.annotate(
            student_count=Count('enrollment', filter=Q(enrollment__status='enrolled'))
        ).values('course_name', 'student_count').order_by('-student_count')[:10]

        enrollment_count_data = {
            'categories': [item['course_name'] for item in enrollment_count],
            'values': [item['student_count'] for item in enrollment_count]
        }

        # 2. 课程分布（按状态）
        course_distribution = course_queryset.values('status').annotate(
            count=Count('id')
        ).order_by('status')

        course_distribution_data = {
            'items': [
                {
                    'name': dict(Course.STATUS_CHOICES).get(item['status'], item['status']),
                    'value': item['count']
                }
                for item in course_distribution
            ]
        }

        # 3. 院系对比
        department_comparison = User.objects.filter(role='teacher').values('department').annotate(
            course_count=Count('course'),
            student_count=Count('course__enrollment', filter=Q(course__enrollment__status='enrolled'))
        ).order_by('-course_count')[:8]

        department_comparison_data = {
            'categories': [item['department'] or '未设置' for item in department_comparison],
            'values': [item['course_count'] for item in department_comparison]
        }

        # 4. 成绩等级分布
        grade_levels = enrollment_queryset.filter(
            grade__isnull=False
        ).values('grade').annotate(count=Count('id')).order_by('grade')

        grade_levels_data = {
            'categories': [item['grade'] for item in grade_levels],
            'values': [item['count'] for item in grade_levels]
        }

        # 5. 教师排行（按学生数）
        teacher_ranking = User.objects.filter(role='teacher').annotate(
            student_count=Count('course__enrollment', filter=Q(course__enrollment__status='enrolled'))
        ).order_by('-student_count')[:10]

        teacher_ranking_data = {
            'categories': [teacher.real_name for teacher in teacher_ranking],
            'values': [teacher.student_count for teacher in teacher_ranking]
        }

        # 6. 热门课程（按选课人数）
        popular_courses = course_queryset.annotate(
            student_count=Count('enrollment', filter=Q(enrollment__status='enrolled'))
        ).order_by('-student_count')[:10]

        popular_courses_data = {
            'categories': [course.course_name for course in popular_courses],
            'values': [course.student_count for course in popular_courses]
        }

        # 7. 趋势数据（最近30天选课趋势）
        trend_data = self._get_enrollment_trend_data(enrollment_queryset, 30)

        # 8. 分布分析（选课状态分布）
        enrollment_status_distribution = enrollment_queryset.values('status').annotate(
            count=Count('id')
        ).order_by('status')

        distribution_data = {
            'items': [
                {
                    'name': dict(Enrollment.STATUS_CHOICES).get(item['status'], item['status']),
                    'value': item['count']
                }
                for item in enrollment_status_distribution
            ]
        }

        # 9. 雷达图数据（系统综合评估）
        radar_data = {
            'indicators': [
                {'name': '课程数量', 'max': 100},
                {'name': '选课率', 'max': 100},
                {'name': '完成率', 'max': 100},
                {'name': '平均成绩', 'max': 100},
                {'name': '教师活跃度', 'max': 100},
                {'name': '学生满意度', 'max': 100}
            ],
            'data': [
                {
                    'name': '系统指标',
                    'value': [
                        min(course_queryset.count() * 2, 100),  # 课程数量指标
                        85,  # 选课率
                        78,  # 完成率
                        min(enrollment_queryset.filter(total_score__isnull=False).aggregate(avg=Avg('total_score'))[
                                'avg'] or 0, 100),  # 平均成绩
                        90,  # 教师活跃度
                        82  # 学生满意度
                    ]
                }
            ]
        }

        # 10. 热力图数据（选课时间分布）
        heatmap_data = self._get_enrollment_heatmap_data(enrollment_queryset)

        # 11. 仪表盘数据（系统健康度）
        total_capacity = course_queryset.aggregate(total=Sum('capacity'))['total'] or 1
        total_enrolled = enrollment_queryset.filter(status='enrolled').count()
        system_health = min(round((total_enrolled / total_capacity) * 100, 1), 100)

        gauge_data = {
            'value': system_health,
            'name': '系统使用率',
            'max_value': 100
        }

        return {
            'enrollment_count': enrollment_count_data,
            'course_distribution': course_distribution_data,
            'department_comparison': department_comparison_data,
            'grade_levels': grade_levels_data,
            'teacher_ranking': teacher_ranking_data,
            'popular_courses': popular_courses_data,
            'trend': trend_data,
            'distribution': distribution_data,
            'radar': radar_data,
            'heatmap': heatmap_data,
            'gauge': gauge_data
        }

    def _get_teacher_charts(self, teacher_id, semester, start_date, end_date):
        """教师图表数据"""
        # 基础查询集
        course_queryset = Course.objects.filter(teacher_id=teacher_id)
        enrollment_queryset = Enrollment.objects.filter(course__teacher_id=teacher_id)

        # 应用过滤条件
        if semester:
            course_queryset = course_queryset.filter(semester=semester)
            enrollment_queryset = enrollment_queryset.filter(course__semester=semester)

        # 1. 课程选课情况
        course_enrollment = course_queryset.annotate(
            student_count=Count('enrollment', filter=Q(enrollment__status='enrolled'))
        ).values('course_name', 'student_count', 'capacity')

        course_enrollment_data = {
            'categories': [item['course_name'] for item in course_enrollment],
            'values': [item['student_count'] for item in course_enrollment]
        }

        # 2. 成绩分布
        grade_distribution = enrollment_queryset.filter(
            grade__isnull=False
        ).values('grade').annotate(count=Count('id')).order_by('grade')

        grade_distribution_data = {
            'items': [
                {
                    'name': f"{item['grade']}等",
                    'value': item['count']
                }
                for item in grade_distribution
            ]
        }

        # 3. 学期对比
        semester_comparison = Course.objects.filter(teacher_id=teacher_id).values('semester').annotate(
            course_count=Count('id'),
            student_count=Count('enrollment', filter=Q(enrollment__status='enrolled'))
        ).order_by('-semester')[:6]

        semester_comparison_data = {
            'categories': [item['semester'] for item in semester_comparison],
            'values': [item['student_count'] for item in semester_comparison]
        }

        # 4. 学生表现
        student_performance = enrollment_queryset.filter(
            total_score__isnull=False
        ).aggregate(
            excellent=Count('id', filter=Q(total_score__gte=90)),
            good=Count('id', filter=Q(total_score__gte=80, total_score__lt=90)),
            fair=Count('id', filter=Q(total_score__gte=70, total_score__lt=80)),
            poor=Count('id', filter=Q(total_score__gte=60, total_score__lt=70)),
            fail=Count('id', filter=Q(total_score__lt=60))
        )

        student_performance_data = {
            'categories': ['优秀', '良好', '中等', '及格', '不及格'],
            'values': [
                student_performance['excellent'],
                student_performance['good'],
                student_performance['fair'],
                student_performance['poor'],
                student_performance['fail']
            ]
        }

        # 5. 课程对比
        course_comparison = course_queryset.annotate(
            avg_score=Avg('enrollment__total_score'),
            student_count=Count('enrollment', filter=Q(enrollment__status='enrolled'))
        ).values('course_name', 'avg_score', 'student_count')[:8]

        course_comparison_data = {
            'categories': [item['course_name'] for item in course_comparison],
            'values': [round(item['avg_score'] or 0, 1) for item in course_comparison]
        }

        # 6. 教学效果（完成率）
        teaching_effect = enrollment_queryset.filter(
            status__in=['enrolled', 'completed']
        ).aggregate(
            total=Count('id'),
            completed=Count('id', filter=Q(status='completed'))
        )

        completion_rate = round(
            (teaching_effect['completed'] / teaching_effect['total'] * 100) if teaching_effect['total'] > 0 else 0,
            1
        )

        teaching_effect_data = {
            'categories': ['已完成', '进行中'],
            'values': [teaching_effect['completed'], teaching_effect['total'] - teaching_effect['completed']]
        }

        # 趋势数据
        trend_data = self._get_enrollment_trend_data(enrollment_queryset, 30)

        # 分布数据
        distribution_data = {
            'items': [
                {'name': '已选课', 'value': enrollment_queryset.filter(status='enrolled').count()},
                {'name': '已完成', 'value': enrollment_queryset.filter(status='completed').count()},
                {'name': '已退选', 'value': enrollment_queryset.filter(status='dropped').count()}
            ]
        }

        # 雷达图数据
        avg_score = enrollment_queryset.filter(total_score__isnull=False).aggregate(avg=Avg('total_score'))['avg'] or 0
        radar_data = {
            'indicators': [
                {'name': '课程质量', 'max': 100},
                {'name': '学生参与度', 'max': 100},
                {'name': '完成率', 'max': 100},
                {'name': '平均成绩', 'max': 100},
                {'name': '课程受欢迎度', 'max': 100}
            ],
            'data': [
                {
                    'name': '教学质量',
                    'value': [
                        min(avg_score, 100),
                        85,  # 学生参与度
                        completion_rate,
                        min(avg_score, 100),
                        80  # 课程受欢迎度
                    ]
                }
            ]
        }

        # 热力图和仪表盘数据
        heatmap_data = self._get_enrollment_heatmap_data(enrollment_queryset)
        gauge_data = {
            'value': completion_rate,
            'name': '教学效果指数',
            'max_value': 100
        }

        return {
            'course_enrollment': course_enrollment_data,
            'grade_distribution': grade_distribution_data,
            'semester_comparison': semester_comparison_data,
            'student_performance': student_performance_data,
            'course_comparison': course_comparison_data,
            'teaching_effect': teaching_effect_data,
            'trend': trend_data,
            'distribution': distribution_data,
            'radar': radar_data,
            'heatmap': heatmap_data,
            'gauge': gauge_data
        }

    def _get_student_charts(self, student_id, semester, start_date, end_date):
        """学生图表数据"""
        # 基础查询集
        enrollment_queryset = Enrollment.objects.filter(student_id=student_id)

        # 应用过滤条件
        if semester:
            enrollment_queryset = enrollment_queryset.filter(course__semester=semester)

        # 1. 我的成绩分布
        my_grades = enrollment_queryset.filter(
            grade__isnull=False
        ).values('grade').annotate(count=Count('id')).order_by('grade')

        my_grades_data = {
            'items': [
                {
                    'name': f"{item['grade']}等",
                    'value': item['count']
                }
                for item in my_grades
            ]
        }

        # 2. 学分统计
        credit_stats = Course.objects.filter(
            id__in=enrollment_queryset.values('course_id')
        ).aggregate(
            total_credits=Sum('credit_hours'),
            earned_credits=Sum('credit_hours', filter=Q(
                enrollment__student_id=student_id,
                enrollment__status='completed',
                enrollment__total_score__gte=60
            ))
        )

        credit_stats_data = {
            'categories': ['已获得学分', '总学分'],
            'values': [
                credit_stats['earned_credits'] or 0,
                credit_stats['total_credits'] or 0
            ]
        }

        # 3. 课程进度
        course_progress = enrollment_queryset.values('status').annotate(
            count=Count('id')
        ).order_by('status')

        course_progress_data = {
            'items': [
                {
                    'name': dict(Enrollment.STATUS_CHOICES).get(item['status'], item['status']),
                    'value': item['count']
                }
                for item in course_progress
            ]
        }

        # 4. 学习进度（按学期）
        study_progress = Course.objects.filter(
            id__in=enrollment_queryset.values('course_id')
        ).values('semester').annotate(
            enrolled=Count('enrollment', filter=Q(enrollment__student_id=student_id, enrollment__status='enrolled')),
            completed=Count('enrollment', filter=Q(enrollment__student_id=student_id, enrollment__status='completed'))
        ).order_by('-semester')[:6]

        study_progress_data = {
            'categories': [item['semester'] for item in study_progress],
            'values': [item['completed'] for item in study_progress]
        }

        # 5. 成绩趋势（按学期）
        grade_trend = enrollment_queryset.filter(
            total_score__isnull=False
        ).values('course__semester').annotate(
            avg_score=Avg('total_score')
        ).order_by('-course__semester')[:6]

        grade_trend_data = {
            'categories': [item['course__semester'] for item in grade_trend],
            'values': [round(item['avg_score'], 1) for item in grade_trend]
        }

        # 6. 课程完成情况
        completion_stats = enrollment_queryset.aggregate(
            total=Count('id'),
            completed=Count('id', filter=Q(status='completed')),
            enrolled=Count('id', filter=Q(status='enrolled')),
            dropped=Count('id', filter=Q(status='dropped'))
        )

        course_completion_data = {
            'categories': ['已完成', '进行中', '已退选'],
            'values': [
                completion_stats['completed'],
                completion_stats['enrolled'],
                completion_stats['dropped']
            ]
        }

        # 趋势数据
        trend_data = self._get_enrollment_trend_data(enrollment_queryset, 30)

        # 分布数据
        distribution_data = {
            'items': course_progress_data['items']
        }

        # 雷达图数据
        graded_enrollments = enrollment_queryset.filter(total_score__isnull=False)
        avg_score = graded_enrollments.aggregate(avg=Avg('total_score'))['avg'] or 0
        completion_rate = (completion_stats['completed'] / completion_stats['total'] * 100) if completion_stats[
                                                                                                   'total'] > 0 else 0

        radar_data = {
            'indicators': [
                {'name': '学习态度', 'max': 100},
                {'name': '成绩水平', 'max': 100},
                {'name': '完成率', 'max': 100},
                {'name': '出勤率', 'max': 100},
                {'name': '作业质量', 'max': 100}
            ],
            'data': [
                {
                    'name': '学习能力',
                    'value': [
                        min(completion_rate + 10, 100),  # 学习态度
                        min(avg_score, 100),  # 成绩水平
                        completion_rate,  # 完成率
                        85,  # 出勤率（可以从考勤成绩计算）
                        80  # 作业质量（可以从作业成绩计算）
                    ]
                }
            ]
        }

        # 热力图和仪表盘数据
        heatmap_data = self._get_enrollment_heatmap_data(enrollment_queryset)
        gauge_data = {
            'value': completion_rate,
            'name': '学习完成度',
            'max_value': 100
        }

        return {
            'my_grades': my_grades_data,
            'credit_stats': credit_stats_data,
            'course_progress': course_progress_data,
            'study_progress': study_progress_data,
            'grade_trend': grade_trend_data,
            'course_completion': course_completion_data,
            'trend': trend_data,
            'distribution': distribution_data,
            'radar': radar_data,
            'heatmap': heatmap_data,
            'gauge': gauge_data
        }

    def _get_enrollment_trend_data(self, queryset, days=30):
        """获取选课趋势数据"""
        trend_data = {
            'categories': [],
            'series_names': ['选课数量'],
            'series': [[]]
        }

        for i in range(days):
            date = timezone.now().date() - timedelta(days=days - 1 - i)
            count = queryset.filter(
                enrollment_date__date=date,
                status='enrolled'
            ).count()

            trend_data['categories'].append(date.strftime('%m-%d'))
            trend_data['series'][0].append(count)

        return trend_data

    def _get_enrollment_heatmap_data(self, queryset):
        """获取选课热力图数据"""
        # 按小时和星期统计选课数据
        heatmap_data = {
            'hours': [f"{i}:00" for i in range(24)],
            'days': ['周一', '周二', '周三', '周四', '周五', '周六', '周日'],
            'data': [],
            'max_value': 0
        }

        # 生成模拟热力图数据（实际项目中应该根据真实的选课时间统计）
        max_val = 0
        for day in range(7):
            for hour in range(24):
                # 工作时间选课较多
                if 8 <= hour <= 22:
                    value = queryset.filter(
                        enrollment_date__week_day=day + 2,  # Django的week_day从2开始（周一）
                        enrollment_date__hour=hour
                    ).count()
                else:
                    value = 0

                heatmap_data['data'].append([hour, day, value])
                max_val = max(max_val, value)

        heatmap_data['max_value'] = max_val

        return heatmap_data


class StatisticsTableDataView(APIView):
    """统计表格数据视图"""

    authentication_classes = []
    permission_classes = [AllowAny]

    def get(self, request):
        """获取表格数据"""
        try:
            user_id = request.META.get('HTTP_USER_ID')
            user_role = request.META.get('HTTP_USER_ROLE')

            if not user_id:
                return Response({
                    'code': 401,
                    'message': '未登录'
                }, status=status.HTTP_401_UNAUTHORIZED)

            # 获取查询参数
            tab = request.GET.get('tab', '')
            page = int(request.GET.get('page', 1))
            page_size = int(request.GET.get('page_size', 20))
            semester = request.GET.get('semester', '')

            # 根据角色和tab返回不同数据
            if user_role == 'admin':
                data = self._get_admin_table_data(tab, page, page_size, semester)
            elif user_role == 'teacher':
                data = self._get_teacher_table_data(user_id, tab, page, page_size, semester)
            elif user_role == 'student':
                data = self._get_student_table_data(user_id, tab, page, page_size, semester)
            else:
                return Response({
                    'code': 403,
                    'message': '无效的用户角色'
                }, status=status.HTTP_403_FORBIDDEN)

            return Response({
                'code': 200,
                'message': '获取表格数据成功',
                'data': data
            }, status=status.HTTP_200_OK)

        except Exception as e:
            logger.error(f"获取表格数据失败: {str(e)}")
            return Response({
                'code': 500,
                'message': '获取表格数据失败'
            }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

    def _get_admin_table_data(self, tab, page, page_size, semester):
        """管理员表格数据"""
        if tab == 'courses':
            # 课程统计表
            queryset = Course.objects.select_related('teacher').annotate(
                enrolled_count_calc=Count('enrollment', filter=Q(enrollment__status='enrolled')),
                avg_score=Avg('enrollment__total_score'),
                pass_rate=Case(
                    When(enrollment__isnull=True, then=0),
                    default=Count('enrollment', filter=Q(enrollment__total_score__gte=60)) * 100.0 / Count('enrollment',
                                                                                                           filter=Q(
                                                                                                               enrollment__total_score__isnull=False)),
                    output_field=FloatField()
                )
            )

            if semester:
                queryset = queryset.filter(semester=semester)

            # 分页
            total = queryset.count()
            start = (page - 1) * page_size
            end = start + page_size
            courses = queryset[start:end]

            items = []
            for course in courses:
                items.append({
                    'course_name': course.course_name,
                    'teacher_name': course.teacher.real_name,
                    'enrolled_count': course.enrolled_count_calc,
                    'capacity': course.capacity,
                    'enrollment_rate': round((course.enrolled_count_calc / course.capacity * 100),
                                             1) if course.capacity > 0 else 0,
                    'avg_score': round(course.avg_score, 1) if course.avg_score else 0,
                    'pass_rate': round(course.pass_rate, 1) if course.pass_rate else 0,
                    'semester': course.semester
                })

            return {'items': items, 'total': total}

        elif tab == 'teachers':
            # 教师统计表
            queryset = User.objects.filter(role='teacher').annotate(
                course_count=Count('course'),
                student_count=Count('course__enrollment', filter=Q(course__enrollment__status='enrolled')),
                avg_score=Avg('course__enrollment__total_score'),
                pass_rate=Case(
                    When(course__enrollment__isnull=True, then=0),
                    default=Count('course__enrollment',
                                  filter=Q(course__enrollment__total_score__gte=60)) * 100.0 / Count(
                        'course__enrollment', filter=Q(course__enrollment__total_score__isnull=False)),
                    output_field=FloatField()
                )
            )

            # 分页
            total = queryset.count()
            start = (page - 1) * page_size
            end = start + page_size
            teachers = queryset[start:end]

            items = []
            for teacher in teachers:
                items.append({
                    'teacher_name': teacher.real_name,
                    'department': teacher.department or '未设置',
                    'course_count': teacher.course_count,
                    'student_count': teacher.student_count,
                    'avg_score': round(teacher.avg_score, 1) if teacher.avg_score else 0,
                    'pass_rate': round(teacher.pass_rate, 1) if teacher.pass_rate else 0,
                    'rating': 4.5  # 可以添加教师评分逻辑
                })

            return {'items': items, 'total': total}

        elif tab == 'students':
            # 学生统计表
            queryset = User.objects.filter(role='student').annotate(
                enrolled_count=Count('enrollment', filter=Q(enrollment__status='enrolled')),
                completed_count=Count('enrollment', filter=Q(enrollment__status='completed')),
                avg_score=Avg('enrollment__total_score'),
                total_credits=Sum('enrollment__course__credit_hours',
                                  filter=Q(enrollment__status__in=['enrolled', 'completed']))
            )

            # 分页
            total = queryset.count()
            start = (page - 1) * page_size
            end = start + page_size
            students = queryset[start:end]

            items = []
            for student in students:
                items.append({
                    'student_name': student.real_name,
                    'student_id': student.student_id or '未设置',
                    'department': student.department or '未设置',
                    'enrolled_count': student.enrolled_count,
                    'completed_count': student.completed_count,
                    'avg_score': round(student.avg_score, 1) if student.avg_score else 0,
                    'total_credits': student.total_credits or 0
                })

            return {'items': items, 'total': total}

        elif tab == 'departments':
            # 院系统计表
            queryset = User.objects.filter(role='teacher').values('department').annotate(
                teacher_count=Count('id'),
                course_count=Count('course'),
                student_count=Count('course__enrollment', filter=Q(course__enrollment__status='enrolled')),
                avg_score=Avg('course__enrollment__total_score')
            ).exclude(department__isnull=True).order_by('-course_count')

            # 简单分页处理
            total = queryset.count()
            start = (page - 1) * page_size
            end = start + page_size
            departments = list(queryset[start:end])

            items = []
            for dept in departments:
                items.append({
                    'department': dept['department'],
                    'teacher_count': dept['teacher_count'],
                    'course_count': dept['course_count'],
                    'student_count': dept['student_count'],
                    'avg_score': round(dept['avg_score'], 1) if dept['avg_score'] else 0
                })

            return {'items': items, 'total': total}

        return {'items': [], 'total': 0}

    def _get_teacher_table_data(self, teacher_id, tab, page, page_size, semester):
        """教师表格数据"""
        if tab == 'my_courses':
            # 我的课程表
            queryset = Course.objects.filter(teacher_id=teacher_id).annotate(
                enrolled_count_calc=Count('enrollment', filter=Q(enrollment__status='enrolled')),
                completed_count=Count('enrollment', filter=Q(enrollment__status='completed')),
                avg_score=Avg('enrollment__total_score'),
                pass_rate=Case(
                    When(enrollment__isnull=True, then=0),
                    default=Count('enrollment', filter=Q(enrollment__total_score__gte=60)) * 100.0 / Count('enrollment',
                                                                                                           filter=Q(
                                                                                                               enrollment__total_score__isnull=False)),
                    output_field=FloatField()
                )
            )

            if semester:
                queryset = queryset.filter(semester=semester)

            # 分页
            total = queryset.count()
            start = (page - 1) * page_size
            end = start + page_size
            courses = queryset[start:end]

            items = []
            for course in courses:
                items.append({
                    'course_name': course.course_name,
                    'enrolled_count': course.enrolled_count_calc,
                    'completed_count': course.completed_count,
                    'avg_score': round(course.avg_score, 1) if course.avg_score else 0,
                    'pass_rate': round(course.pass_rate, 1) if course.pass_rate else 0,
                    'semester': course.semester
                })

            return {'items': items, 'total': total}

        elif tab == 'students':
            # 学生表现表
            queryset = Enrollment.objects.filter(
                course__teacher_id=teacher_id
            ).select_related('student', 'course')

            if semester:
                queryset = queryset.filter(course__semester=semester)

            # 分页
            total = queryset.count()
            start = (page - 1) * page_size
            end = start + page_size
            enrollments = queryset[start:end]

            items = []
            for enrollment in enrollments:
                items.append({
                    'student_name': enrollment.student.real_name,
                    'student_id': enrollment.student.student_id or '未设置',
                    'course_name': enrollment.course.course_name,
                    'total_score': float(enrollment.total_score) if enrollment.total_score else 0,
                    'grade': enrollment.grade or '未评分',
                    'status': enrollment.get_status_display()
                })

            return {'items': items, 'total': total}

        elif tab == 'grades':
            # 成绩统计表
            queryset = Enrollment.objects.filter(
                course__teacher_id=teacher_id,
                total_score__isnull=False
            ).values('course__course_name', 'grade').annotate(
                count=Count('id')
            ).order_by('course__course_name', 'grade')

            # 简单分页处理
            total = len(queryset)
            start = (page - 1) * page_size
            end = start + page_size
            grade_stats = list(queryset[start:end])

            items = []
            for stat in grade_stats:
                items.append({
                    'course_name': stat['course__course_name'],
                    'grade': stat['grade'],
                    'count': stat['count']
                })

            return {'items': items, 'total': total}

        return {'items': [], 'total': 0}

    def _get_student_table_data(self, student_id, tab, page, page_size, semester):
        """学生表格数据"""
        if tab == 'my_enrollments':
            # 我的选课表
            queryset = Enrollment.objects.filter(
                student_id=student_id
            ).select_related('course', 'course__teacher')

            if semester:
                queryset = queryset.filter(course__semester=semester)

            # 分页
            total = queryset.count()
            start = (page - 1) * page_size
            end = start + page_size
            enrollments = queryset[start:end]

            items = []
            for enrollment in enrollments:
                items.append({
                    'course_name': enrollment.course.course_name,
                    'teacher_name': enrollment.course.teacher.real_name,
                    'credit_hours': enrollment.course.credit_hours,
                    'total_score': float(enrollment.total_score) if enrollment.total_score else 0,
                    'grade': enrollment.grade or '未评分',
                    'status': enrollment.get_status_display(),
                    'semester': enrollment.course.semester
                })

            return {'items': items, 'total': total}

        elif tab == 'grades':
            # 成绩记录表
            queryset = Enrollment.objects.filter(
                student_id=student_id,
                total_score__isnull=False
            ).select_related('course', 'course__teacher')

            if semester:
                queryset = queryset.filter(course__semester=semester)

            # 分页
            total = queryset.count()
            start = (page - 1) * page_size
            end = start + page_size
            enrollments = queryset[start:end]

            items = []
            for enrollment in enrollments:
                items.append({
                    'course_name': enrollment.course.course_name,
                    'teacher_name': enrollment.course.teacher.real_name,
                    'attendance_score': float(enrollment.attendance_score) if enrollment.attendance_score else 0,
                    'homework_score': float(enrollment.homework_score) if enrollment.homework_score else 0,
                    'midterm_score': float(enrollment.midterm_score) if enrollment.midterm_score else 0,
                    'final_score': float(enrollment.final_score) if enrollment.final_score else 0,
                    'total_score': float(enrollment.total_score),
                    'grade': enrollment.grade,
                    'semester': enrollment.course.semester
                })

            return {'items': items, 'total': total}

        elif tab == 'progress':
            # 学习进度表
            queryset = Course.objects.filter(
                id__in=Enrollment.objects.filter(student_id=student_id).values('course_id')
            ).annotate(
                my_status=Case(
                    When(enrollment__student_id=student_id, then=F('enrollment__status')),
                    default='not_enrolled'
                ),
                my_score=Case(
                    When(enrollment__student_id=student_id, then=F('enrollment__total_score')),
                    default=None,
                    output_field=FloatField()
                )
            ).select_related('teacher')

            if semester:
                queryset = queryset.filter(semester=semester)

            # 分页
            total = queryset.count()
            start = (page - 1) * page_size
            end = start + page_size
            courses = queryset[start:end]

            items = []
            for course in courses:
                # 计算进度百分比
                if course.my_status == 'completed':
                    progress = 100
                elif course.my_status == 'enrolled' and course.my_score:
                    progress = 80  # 已有成绩但未完成
                elif course.my_status == 'enrolled':
                    progress = 50  # 已选课但无成绩
                else:
                    progress = 0

                items.append({
                    'course_name': course.course_name,
                    'teacher_name': course.teacher.real_name,
                    'credit_hours': course.credit_hours,
                    'status': dict(Enrollment.STATUS_CHOICES).get(course.my_status, '未选课'),
                    'progress': progress,
                    'semester': course.semester
                })

            return {'items': items, 'total': total}

        return {'items': [], 'total': 0}


class StatisticsExportView(APIView):
    """统计数据导出视图"""

    authentication_classes = []
    permission_classes = [AllowAny]

    def get(self, request):
        """导出统计报告"""
        try:
            user_id = request.META.get('HTTP_USER_ID')
            user_role = request.META.get('HTTP_USER_ROLE')

            if not user_id:
                return Response({
                    'code': 401,
                    'message': '未登录'
                }, status=status.HTTP_401_UNAUTHORIZED)

            if user_role not in ['admin', 'teacher']:
                return Response({
                    'code': 403,
                    'message': '权限不足'
                }, status=status.HTTP_403_FORBIDDEN)

            # 获取查询参数
            semester = request.GET.get('semester', '')
            start_date = request.GET.get('start_date', '')
            end_date = request.GET.get('end_date', '')

            # 创建Excel文件
            workbook = openpyxl.Workbook()

            # 根据角色生成不同的报告
            if user_role == 'admin':
                self._generate_admin_report(workbook, semester, start_date, end_date)
            else:
                self._generate_teacher_report(workbook, user_id, semester, start_date, end_date)

            # 保存到内存
            excel_file = BytesIO()
            workbook.save(excel_file)
            excel_file.seek(0)

            # 创建HTTP响应
            response = HttpResponse(
                excel_file.read(),
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )

            filename = f"统计报告_{user_role}_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
            response['Content-Disposition'] = f'attachment; filename="{filename}"'

            return response

        except Exception as e:
            logger.error(f"导出统计报告失败: {str(e)}")
            return Response({
                'code': 500,
                'message': '导出统计报告失败'
            }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

    def _generate_admin_report(self, workbook, semester, start_date, end_date):
        """生成管理员报告"""
        # 删除默认工作表
        workbook.remove(workbook.active)

        # 1. 概览数据工作表
        overview_ws = workbook.create_sheet("统计概览")
        self._add_overview_sheet(overview_ws, 'admin', None, semester, start_date, end_date)

        # 2. 课程统计工作表
        course_ws = workbook.create_sheet("课程统计")
        self._add_course_statistics_sheet(course_ws, semester)

        # 3. 教师统计工作表
        teacher_ws = workbook.create_sheet("教师统计")
        self._add_teacher_statistics_sheet(teacher_ws, semester)

        # 4. 学生统计工作表
        student_ws = workbook.create_sheet("学生统计")
        self._add_student_statistics_sheet(student_ws, semester)

    def _generate_teacher_report(self, workbook, teacher_id, semester, start_date, end_date):
        """生成教师报告"""
        # 删除默认工作表
        workbook.remove(workbook.active)

        # 1. 概览数据工作表
        overview_ws = workbook.create_sheet("教学概览")
        self._add_overview_sheet(overview_ws, 'teacher', teacher_id, semester, start_date, end_date)

        # 2. 课程统计工作表
        course_ws = workbook.create_sheet("我的课程")
        self._add_teacher_course_sheet(course_ws, teacher_id, semester)

        # 3. 学生表现工作表
        student_ws = workbook.create_sheet("学生表现")
        self._add_teacher_student_sheet(student_ws, teacher_id, semester)

    def _add_overview_sheet(self, worksheet, role, user_id, semester, start_date, end_date):
        """添加概览数据工作表"""
        # 设置标题
        title_font = Font(bold=True, size=14)
        header_font = Font(bold=True)

        worksheet['A1'] = f"{'全局' if role == 'admin' else '教学'}统计概览报告"
        worksheet['A1'].font = title_font

        # 添加生成时间
        worksheet['A2'] = f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"

        # 添加筛选条件
        row = 4
        if semester:
            worksheet[f'A{row}'] = f"学期: {semester}"
            row += 1
        if start_date and end_date:
            worksheet[f'A{row}'] = f"时间范围: {start_date} 至 {end_date}"
            row += 1

        # 添加统计数据
        row += 2
        if role == 'admin':
            data = self._get_admin_overview(semester, start_date, end_date)
            stats = [
                ('总选课数', data['total_enrollments']),
                ('活跃学生数', data['active_students']),
                ('开设课程数', data['total_courses']),
                ('平均成绩', data['avg_score']),
                ('总用户数', data['total_users']),
                ('教师数', data['total_teachers'])
            ]
        else:
            data = self._get_teacher_overview(user_id, semester, start_date, end_date)
            stats = [
                ('我的课程数', data['my_courses']),
                ('教学学生数', data['my_students']),
                ('完成率(%)', data['completion_rate']),
                ('平均成绩', data['avg_score'])
            ]

        worksheet[f'A{row}'] = '统计项目'
        worksheet[f'B{row}'] = '数值'
        worksheet[f'A{row}'].font = header_font
        worksheet[f'B{row}'].font = header_font
        row += 1

        for name, value in stats:
            worksheet[f'A{row}'] = name
            worksheet[f'B{row}'] = value
            row += 1

    def _add_course_statistics_sheet(self, worksheet, semester):
        """添加课程统计工作表"""
        # 设置表头
        headers = ['课程名称', '授课教师', '选课人数', '课程容量', '选课率(%)', '平均成绩', '通过率(%)', '学期']
        for col, header in enumerate(headers, 1):
            cell = worksheet.cell(row=1, column=col, value=header)
            cell.font = Font(bold=True)

        # 获取数据
        queryset = Course.objects.select_related('teacher').annotate(
            enrolled_count_calc=Count('enrollment', filter=Q(enrollment__status='enrolled')),
            avg_score=Avg('enrollment__total_score'),
            pass_rate=Case(
                When(enrollment__isnull=True, then=0),
                default=Count('enrollment', filter=Q(enrollment__total_score__gte=60)) * 100.0 / Count('enrollment',
                                                                                                       filter=Q(
                                                                                                           enrollment__total_score__isnull=False)),
                output_field=FloatField()
            )
        )

        if semester:
            queryset = queryset.filter(semester=semester)

        # 写入数据
        for row, course in enumerate(queryset, 2):
            worksheet.cell(row=row, column=1, value=course.course_name)
            worksheet.cell(row=row, column=2, value=course.teacher.real_name)
            worksheet.cell(row=row, column=3, value=course.enrolled_count_calc)
            worksheet.cell(row=row, column=4, value=course.capacity)
            worksheet.cell(row=row, column=5, value=round((course.enrolled_count_calc / course.capacity * 100),
                                                          1) if course.capacity > 0 else 0)
            worksheet.cell(row=row, column=6, value=round(course.avg_score, 1) if course.avg_score else 0)
            worksheet.cell(row=row, column=7, value=round(course.pass_rate, 1) if course.pass_rate else 0)
            worksheet.cell(row=row, column=8, value=course.semester)

    def _add_teacher_statistics_sheet(self, worksheet, semester):
        """添加教师统计工作表"""
        # 设置表头
        headers = ['教师姓名', '院系', '开设课程数', '教学学生数', '平均成绩', '通过率(%)']
        for col, header in enumerate(headers, 1):
            cell = worksheet.cell(row=1, column=col, value=header)
            cell.font = Font(bold=True)

        # 获取数据
        queryset = User.objects.filter(role='teacher').annotate(
            course_count=Count('course'),
            student_count=Count('course__enrollment', filter=Q(course__enrollment__status='enrolled')),
            avg_score=Avg('course__enrollment__total_score'),
            pass_rate=Case(
                When(course__enrollment__isnull=True, then=0),
                default=Count('course__enrollment', filter=Q(course__enrollment__total_score__gte=60)) * 100.0 / Count(
                    'course__enrollment', filter=Q(course__enrollment__total_score__isnull=False)),
                output_field=FloatField()
            )
        )

        # 写入数据
        for row, teacher in enumerate(queryset, 2):
            worksheet.cell(row=row, column=1, value=teacher.real_name)
            worksheet.cell(row=row, column=2, value=teacher.department or '未设置')
            worksheet.cell(row=row, column=3, value=teacher.course_count)
            worksheet.cell(row=row, column=4, value=teacher.student_count)
            worksheet.cell(row=row, column=5, value=round(teacher.avg_score, 1) if teacher.avg_score else 0)
            worksheet.cell(row=row, column=6, value=round(teacher.pass_rate, 1) if teacher.pass_rate else 0)

    def _add_student_statistics_sheet(self, worksheet, semester):
        """添加学生统计工作表"""
        # 设置表头
        headers = ['学生姓名', '学号', '院系', '已选课程数', '已完成课程数', '平均成绩', '总学分']
        for col, header in enumerate(headers, 1):
            cell = worksheet.cell(row=1, column=col, value=header)
            cell.font = Font(bold=True)

        # 获取数据
        queryset = User.objects.filter(role='student').annotate(
            enrolled_count=Count('enrollment', filter=Q(enrollment__status='enrolled')),
            completed_count=Count('enrollment', filter=Q(enrollment__status='completed')),
            avg_score=Avg('enrollment__total_score'),
            total_credits=Sum('enrollment__course__credit_hours',
                              filter=Q(enrollment__status__in=['enrolled', 'completed']))
        )[:1000]  # 限制导出数量

        # 写入数据
        for row, student in enumerate(queryset, 2):
            worksheet.cell(row=row, column=1, value=student.real_name)
            worksheet.cell(row=row, column=2, value=student.student_id or '未设置')
            worksheet.cell(row=row, column=3, value=student.department or '未设置')
            worksheet.cell(row=row, column=4, value=student.enrolled_count)
            worksheet.cell(row=row, column=5, value=student.completed_count)
            worksheet.cell(row=row, column=6, value=round(student.avg_score, 1) if student.avg_score else 0)
            worksheet.cell(row=row, column=7, value=student.total_credits or 0)

    def _add_teacher_course_sheet(self, worksheet, teacher_id, semester):
        """添加教师课程工作表"""
        # 设置表头
        headers = ['课程名称', '选课人数', '完成人数', '平均成绩', '通过率(%)', '学期']
        for col, header in enumerate(headers, 1):
            cell = worksheet.cell(row=1, column=col, value=header)
            cell.font = Font(bold=True)

        # 获取数据
        queryset = Course.objects.filter(teacher_id=teacher_id).annotate(
            enrolled_count_calc=Count('enrollment', filter=Q(enrollment__status='enrolled')),
            completed_count=Count('enrollment', filter=Q(enrollment__status='completed')),
            avg_score=Avg('enrollment__total_score'),
            pass_rate=Case(
                When(enrollment__isnull=True, then=0),
                default=Count('enrollment', filter=Q(enrollment__total_score__gte=60)) * 100.0 / Count('enrollment',
                                                                                                       filter=Q(
                                                                                                           enrollment__total_score__isnull=False)),
                output_field=FloatField()
            )
        )

        if semester:
            queryset = queryset.filter(semester=semester)

        # 写入数据
        for row, course in enumerate(queryset, 2):
            worksheet.cell(row=row, column=1, value=course.course_name)
            worksheet.cell(row=row, column=2, value=course.enrolled_count_calc)
            worksheet.cell(row=row, column=3, value=course.completed_count)
            worksheet.cell(row=row, column=4, value=round(course.avg_score, 1) if course.avg_score else 0)
            worksheet.cell(row=row, column=5, value=round(course.pass_rate, 1) if course.pass_rate else 0)
            worksheet.cell(row=row, column=6, value=course.semester)

    def _add_teacher_student_sheet(self, worksheet, teacher_id, semester):
        """添加教师学生表现工作表"""
        # 设置表头
        headers = ['学生姓名', '学号', '课程名称', '总成绩', '等级成绩', '状态']
        for col, header in enumerate(headers, 1):
            cell = worksheet.cell(row=1, column=col, value=header)
            cell.font = Font(bold=True)

        # 获取数据
        queryset = Enrollment.objects.filter(
            course__teacher_id=teacher_id
        ).select_related('student', 'course')

        if semester:
            queryset = queryset.filter(course__semester=semester)

        queryset = queryset[:1000]  # 限制导出数量

        # 写入数据
        for row, enrollment in enumerate(queryset, 2):
            worksheet.cell(row=row, column=1, value=enrollment.student.real_name)
            worksheet.cell(row=row, column=2, value=enrollment.student.student_id or '未设置')
            worksheet.cell(row=row, column=3, value=enrollment.course.course_name)
            worksheet.cell(row=row, column=4, value=float(enrollment.total_score) if enrollment.total_score else 0)
            worksheet.cell(row=row, column=5, value=enrollment.grade or '未评分')
            worksheet.cell(row=row, column=6, value=enrollment.get_status_display())